package com.snail.common.core.utils.sql;

import com.snail.common.core.constant.CommonConstants;
import com.snail.common.core.exception.UtilException;
import com.snail.common.core.utils.StringUtils;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.schema.Column;

import java.sql.SQLException;
import java.util.Arrays;

/**
 * sql操作工具类
 *
 * @author snail
 */
public class SqlUtil {
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "and |extractvalue|updatexml|exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |or |+|user()";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * like查询字符
     */
    public static String LIKE_CHAR = "%";
    /**
     * 别名加字符
     */
    public static String ALIAS_CHAR = ".";

    public static String LIKE = " like ";
    public static String EQ = " = ";
    public static String IN = " in ";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }

    /**
     * like字符
     *
     * @param value 值
     * @return 结果
     */
    public static String parseLike(Object value) {
        return LIKE_CHAR + value + LIKE_CHAR;
    }

    /**
     * 别名.字段
     *
     * @param alias  别名
     * @param column 字段
     * @return 结果
     */
    public static String aliasColumn(String alias, String column) {
        if (StringUtils.isEmpty(alias)) {
            return column;
        }
        return alias + ALIAS_CHAR + column;
    }

    /**
     * 等于表达式
     *
     * @param alias  别名
     * @param column 字段
     * @param value  值
     * @return 结果
     */
    public static Expression eqExpression(String alias, String column, String value) throws SQLException {
        if(StringUtils.isEmpty(column)){
            throw new SQLException("sql expression column is null");
        }
        if(StringUtils.isEmpty(value)){
            throw new SQLException("sql expression value is null");
        }
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column(aliasColumn(alias, column)));
        equalsTo.setRightExpression(new StringValue(value));
        return equalsTo;
    }

    /**
     * like表达式
     *
     * @param alias  别名
     * @param column 字段
     * @param value  值
     * @return 结果
     */
    public static Expression likeExpression(String alias, String column, Object value) throws SQLException {
        if(StringUtils.isEmpty(column)){
            throw new SQLException("sql expression column is null");
        }
        if(value == null){
            throw new SQLException("sql expression value is null");
        }
        LikeExpression likeExpression = new LikeExpression();
        likeExpression.setLeftExpression(new Column(aliasColumn(alias, column)));
        likeExpression.setRightExpression(new StringValue(parseLike(value)));
        return likeExpression;
    }


    /**
     * like表达式
     *
     * @param alias  别名
     * @param column 字段
     * @param value  值
     * @return 结果
     */
    public static Expression inExpression(String alias, String column, String value) throws SQLException {
        if(StringUtils.isEmpty(column)){
            throw new SQLException("sql expression column is null");
        }
        if(StringUtils.isEmpty(value)){
            throw new SQLException("sql expression value is null");
        }
        InExpression inExpression = new InExpression();
        inExpression.setLeftExpression(new Column(aliasColumn(alias, column)));
        inExpression.setRightItemsList(new ExpressionList(new StringValue(value)));
        return inExpression;
    }


}
